insert overwrite table jms_dwd.dwd_tmsnew_shipment_union_base_dt partition(dt)
select
    id
     ,shipment_no
     ,distribution_type
     ,business_attribute
     ,shipment_name
     ,vehicleline_id
     ,vehicleline_code
     ,vehicleline_name
     ,shifts
     ,transport_type
     ,operation_model
     ,vehicle_groupid
     ,vehicle_typegroup
     ,actual_vehicle_groupid
     ,actual_vehicle_typegroup
     ,start_id
     ,start_code
     ,start_name
     ,end_id
     ,end_code
     ,end_name
     ,cnt
     ,mileage
     ,total_runtime
     ,standard_speed
     ,total_intransittime
     ,span_days
     ,actual_mileage
     ,actual_total_runtime
     ,actual_standard_speed
     ,planned_departure_time
     ,planned_arrival_time
     ,actual_departure_time
     ,actual_arrival_time
     ,force_complete_time
     ,shipment_state
     ,vehicle_class_id
     ,vehicle_class_name
     ,plate_number
     ,trailer_number
     ,carrier_id
     ,carrier_type
     ,carrier_name
     ,ori_reg_shift_carrier_id
     ,vehicletype_id
     ,vehicletype_name
     ,load_weight
     ,vehicle_volume
     ,vehicle_door_cnt
     ,driver_name
     ,driver_contact
     ,create_by
     ,create_by_name
     ,create_time
     ,update_by
     ,update_by_name
     ,update_time
     ,overtime_type
     ,overtime_reasons
     ,shipment_type
     ,quotation_model
     ,apply_reason
     ,audit_status
     ,audit_remark
     ,audit_id
     ,auditer
     ,audit_date
     ,apply_reason_item
     ,pic_path
     ,pic_adr
     ,promotion
     ,freight_generate_status
     ,billing_way
     ,settlement_vehicle_type_id
     ,settlement_vehicle_type_name
     ,car_apply_code
     ,bidding_demand_code
     ,is_ballast
     ,special_vehicle_type_id
     ,special_vehicle_type_name
     ,remark
     ,update_times
     ,start_financial_center_id
     ,start_financial_center_code
     ,start_financial_center_desc
     ,end_financial_center_id
     ,end_financial_center_code
     ,end_financial_center_desc
     ,estimate_arrival_time
     ,opt_net_work_code
     ,opt_net_work_name
     ,opt_financial_center_code
     ,opt_financial_center_desc
     ,start_parent_code
     ,start_parent_name
     ,end_parent_code
     ,end_parent_name
     ,complete_time
     ,table_type
     ,grade
     ,time_effective_shift
     ,overtime_sub_type_code
     ,overtime_sub_type    -- 加班类型子类型
     ,origin_normal_shift_code1   --   原正班单号1
     ,origin_normal_shift_code2    --  原正班单号2
     ,origin_normal_shift_remark    --   原正版单号备注
     ,overtime_type_name   --   加班类型显示名称
     ,second_display_name   --  加班子类型展示业务属性名
     ,repeat_bag_flag    --   循环袋标识 1-是 2-否
     ,adjust_settlement_vehicle_type    --  是否手动调整结算车型 1：已调整
     ,dt
from (
     select
         id
          ,shipment_no
          ,vehicleline_type as distribution_type
          ,business_attribute
          ,shipment_name
          ,vehicleline_id
          ,vehicleline_code
          ,vehicleline_name
          ,shifts
          ,transport_type
          ,operation_model
          ,vehicle_groupid
          ,vehicle_typegroup
          ,vehicletype_id actual_vehicle_groupid
          ,vehicletype_name actual_vehicle_typegroup
          ,start_id
          ,start_code
          ,start_name
          ,end_id
          ,end_code
          ,end_name
          ,cnt
          ,mileage
          ,total_runtime
          ,standard_speed
          ,total_intransittime
          ,span_days
          ,null actual_mileage
          ,null actual_total_runtime
          ,null actual_standard_speed
          ,planned_departure_time
          ,planned_arrival_time
          ,actual_departure_time
          ,actual_arrival_time
          ,null force_complete_time
          ,shipment_state
          ,vehicle_class_id
          ,vehicle_class_name
          ,plate_number
          ,trailer_number
          ,carrier_id
          ,carrier_type
          ,carrier_name
          ,ori_reg_shift_carrier_id
          ,vehicletype_id
          ,vehicletype_name
          ,load_weight
          ,null vehicle_volume
          ,vehicle_door_cnt
          ,driver_name
          ,driver_contact
          ,create_by
          ,create_by_name
          ,create_time
          ,update_by
          ,update_by_name
          ,update_time
          ,overtime_type
          ,overtime_reasons
          ,shipment_type
          ,quotation_model
          ,apply_reason
          ,audit_status
          ,audit_remark
          ,audit_id
          ,auditer
          ,audit_date
          ,apply_reason_item
          ,pic_path
          ,pic_adr
          ,promotion
          ,freight_generate_status
          ,billing_way
          ,settlement_vehicle_type_id
          ,settlement_vehicle_type_name
          ,car_apply_code
          ,bidding_demand_code
          ,is_ballast
          ,special_vehicle_type_id
          ,special_vehicle_type_name
          ,null remark
          ,null update_times
          ,null start_financial_center_id
          ,null start_financial_center_code
          ,null start_financial_center_desc
          ,null end_financial_center_id
          ,null end_financial_center_code
          ,null end_financial_center_desc
          ,null estimate_arrival_time
          ,null opt_net_work_code
          ,null opt_net_work_name
          ,null opt_financial_center_code
          ,null opt_financial_center_desc
          ,null start_parent_code
          ,null start_parent_name
          ,null end_parent_code
          ,null end_parent_name
          ,null complete_time
          ,1 as  table_type
          ,1 as row_id
          ,grade
          ,time_effective_shift
          ,overtime_sub_type_code
           ,overtime_sub_type    -- 加班类型子类型
           ,origin_normal_shift_code1   --   原正班单号1
           ,origin_normal_shift_code2    --  原正班单号2
           ,origin_normal_shift_remark    --   原正版单号备注
           ,overtime_type_name   --   加班类型显示名称
           ,second_display_name   --  加班子类型展示业务属性名
           ,repeat_bag_flag    --   循环袋标识 1-是 2-否
           ,adjust_settlement_vehicle_type    --  是否手动调整结算车型 1：已调整
          ,to_date(planned_departure_time) as dt
     from jms_ods.yl_tmsnew_tms_shipment
     where dt = '{{ execution_date | cst_ds }}'
       and to_date(planned_departure_time) between date_sub('{{ execution_date | cst_ds }}',90) and date_add('{{ execution_date | cst_ds }}',7)

     union all

     select
         id
          ,shipment_no
          ,distribution_type
          ,business_attribute
          ,shipment_name
          ,line_id as vehicleline_id
          ,line_code as vehicleline_code
          ,line_name as vehicleline_name
          ,shifts
          ,null transport_type
          ,null operation_model
          ,vehicle_groupid
          ,vehicle_typegroup
          ,actual_vehicle_groupid
          ,actual_vehicle_typegroup
          ,start_id
          ,start_code
          ,start_name
          ,end_id
          ,end_code
          ,end_name
          ,cnt
          ,mileage
          ,total_runtime
          ,standard_speed
          ,null total_intransittime
          ,null span_days
          ,actual_mileage
          ,actual_total_runtime
          ,actual_standard_speed
          ,planned_departure_time
          ,planned_arrival_time
          ,actual_departure_time
          ,actual_arrival_time
          ,force_complete_time
          ,shipment_state
          ,null vehicle_class_id
          ,null vehicle_class_name
          ,plate_number
          ,null trailer_number
          ,carrier_id
          ,carrier_type
          ,carrier_name
          ,ori_reg_shift_carrier_id
          ,actual_vehicle_groupid vehicletype_id
          ,actual_vehicle_typegroup vehicletype_name
          ,load_weight
          ,vehicle_volume
          ,vehicle_door_cnt
          ,driver_name
          ,driver_contact
          ,create_by
          ,create_by_name
          ,create_time
          ,update_by
          ,update_by_name
          ,update_time
          ,overtime_type
          ,null overtime_reasons
          ,null shipment_type
          ,null quotation_model
          ,null apply_reason
          ,null audit_status
          ,null audit_remark
          ,null audit_id
          ,null auditer
          ,null audit_date
          ,null apply_reason_item
          ,pic_path
          ,pic_adr
          ,null promotion
          ,null freight_generate_status
          ,null billing_way
          ,null settlement_vehicle_type_id
          ,null settlement_vehicle_type_name
          ,null car_apply_code
          ,null bidding_demand_code
          ,null is_ballast
          ,null special_vehicle_type_id
          ,null special_vehicle_type_name
          ,remark
          ,update_times
          ,start_financial_center_id
          ,start_financial_center_code
          ,start_financial_center_desc
          ,end_financial_center_id
          ,end_financial_center_code
          ,end_financial_center_desc
          ,estimate_arrival_time
          ,opt_net_work_code
          ,opt_net_work_name
          ,opt_financial_center_code
          ,opt_financial_center_desc
          ,start_parent_code
          ,start_parent_name
          ,end_parent_code
          ,end_parent_name
          ,complete_time
          ,2 as table_type
          ,row_number() over(partition by shipment_no order by update_time desc) as row_id
          ,null grade
          ,null time_effective_shift
          ,null overtime_sub_type_code
           ,null overtime_sub_type    -- 加班类型子类型
           ,null origin_normal_shift_code1   --   原正班单号1
           ,null origin_normal_shift_code2    --  原正班单号2
           ,null origin_normal_shift_remark    --   原正版单号备注
           ,null overtime_type_name   --   加班类型显示名称
           ,null second_display_name   --  加班子类型展示业务属性名
           ,null repeat_bag_flag    --   循环袋标识 1-是 2-否
           ,null adjust_settlement_vehicle_type    --  是否手动调整结算车型 1：已调整
          ,to_date(planned_departure_time) as dt
     from jms_ods.yl_tmsnew_branch_shipment
     where dt = '{{ execution_date | cst_ds }}'
       and to_date(planned_departure_time) between date_sub('{{ execution_date | cst_ds }}',90) and date_add('{{ execution_date | cst_ds }}',7)
) tmp where row_id = 1
distribute by dt;